package com.example.easyexcel.controller;

import com.example.easyexcel.listener.ExcelListener;
import com.example.easyexcel.model.UserModel;
import com.example.easyexcel.model.ok.*;
import com.example.easyexcel.strategy.CustomCellStyleStrategy;
import com.example.easyexcel.strategy.CustomColumnWidthStrategy;
import org.springframework.util.StringUtils;

import java.util.*;
import java.util.stream.Collectors;

import static com.alibaba.excel.EasyExcelFactory.read;
import static com.alibaba.excel.EasyExcelFactory.write;

/**
 * @author dangwen1
 * @date 2021年12月02日 20:55
 * @description
 */
//@Controller
public class OkExcelController {
    public static void main(String[] args) {
        Map<String, String> all = new HashMap<>();
        List<MapRepeatElementModel> repeatElementList = new ArrayList<>();
        Map<String, String> shouWangMap = readExcel1();
        Map<String, String> mengWang = readExcel2();
        Map<String, String> zhongDingDongLiMap = readExcel3();
        Map<String, String> juMengMap = readExcel4();

        Set<Map.Entry<String, String>> jm = juMengMap.entrySet();
        for (Map.Entry<String, String> entry : jm) {
            String key = entry.getKey();
            String value = entry.getValue();
            if (all.containsKey(key)){
                repeatElementList.add(new MapRepeatElementModel(key,value));
            } else {
                all.put(key,value);
            }
        }

        Set<Map.Entry<String, String>> sw = shouWangMap.entrySet();
        for (Map.Entry<String, String> entry : sw) {
            String key = entry.getKey();
            String value = entry.getValue();
            if (all.containsKey(key)){
                repeatElementList.add(new MapRepeatElementModel(key,value));
            } else {
                all.put(key,value);
            }
        }

        Set<Map.Entry<String, String>> mw = mengWang.entrySet();
        for (Map.Entry<String, String> entry : mw) {
            String key = entry.getKey();
            String value = entry.getValue();
            if (all.containsKey(key)){
                repeatElementList.add(new MapRepeatElementModel(key,value));
            } else {
                all.put(key,value);
            }
        }

        Set<Map.Entry<String, String>> zddl = zhongDingDongLiMap.entrySet();
        for (Map.Entry<String, String> entry : zddl) {
            String key = entry.getKey();
            String value = entry.getValue();
            if (all.containsKey(key)){
                repeatElementList.add(new MapRepeatElementModel(key,value));
            } else {
                all.put(key,value);
            }
        }


        System.out.println("all.size():"+all.size()+",repeatElementList.size():"+repeatElementList.size());
        List<MapRepeatElementModel> collect = repeatElementList.stream().sorted(Comparator.comparing(MapRepeatElementModel::getKey)).collect(Collectors.toList());
//        for (MapRepeatElementModel model : collect){
//            System.out.println(model);
//        }
        LinkedHashMap<String, String> sortedMap = all.entrySet().stream().sorted(Map.Entry.comparingByValue()).collect(Collectors.toMap(
                Map.Entry::getKey,
                Map.Entry::getValue,
                (oldVal, newVal) -> oldVal,
                LinkedHashMap::new
        ));
        Set<Map.Entry<String, String>> entries = sortedMap.entrySet();
        Map<String, String> commonEnumMap = new HashMap<>();
        for (Map.Entry<String, String> entry : entries) {
            String key = entry.getKey();
            String value = entry.getValue();
            int i = key.indexOf("_");
            String code = key.substring(i + 1);
            String replacedKey = key.replace("C_", "M_");
            String commonCode = "000";
            String commonMsg = "000";
            if (value.contains("运营商") || value.contains("SMSC")){
                continue;
//                commonCode = "M_MNO_ERROR";
//                commonMsg = "运营商异常";
//                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("黑名单") || value.contains("不在白名单")){
                commonCode = "M_BLACK_LIST_ERROR";
                commonMsg = "黑名单";
                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("签名")){
                continue;
//                commonCode = "M_SIGN_ERROR";
//                commonMsg = "签名错误";
//                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("不成功") || value.contains("未成功") || value.contains("平台")
                    || value.contains("ECSI")|| value.contains("服务代码错误") || value.contains("模版未加退订")){
                continue;
            }
            else if (value.contains("厂家")){
                continue;
//                commonCode = "M_MANUFACTURERS_ERROR";
//                commonMsg = "厂家自定义错误";
//                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("验证码")){
                continue;
//                commonCode = "M_VERIFY_CODE_ERROR";
//                commonMsg = "签名错误";
//                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("非法")){
                continue;
//                commonCode = "M_ILLEGAL_ERROR";
//                commonMsg = "内容非法";
//                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("超时")){
                continue;
//                commonCode = "M_OVER_TIME_ERROR";
//                commonMsg = "超时异常";
//                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("被叫用户")){
                commonCode = "M_PHONE_EMPTY_OFF_ERROR";
                commonMsg = "空号或停机";
                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("短信")){
                commonCode = "M_SMS_ERROR";
                commonMsg = "短信异常";
                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("消息")){
                continue;
//                commonCode = "M_MESSAGE_ERROR";
//                commonMsg = "消息异常";
//                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("频率")){
                commonCode = "M_FREQUENCY_LIMIT_ERROR";
                commonMsg = "频率限制";
                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("流量")){
                commonCode = "M_RATE_OF_FLOW_ERROR";
                commonMsg = "流量限制";
                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("无效") && !value.contains("用户") && !value.contains("号码")){
                continue;
//                commonCode = "M_INVALID_ERROR";
//                commonMsg = "无效的信息";
//                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("无效") && (value.contains("用户") || value.contains("号码"))){
                commonCode = "M_PHONE_DOWN_EMPTY_OFF_ERROR";
                commonMsg = "关机或空号或停机";
                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("通道")){
                continue;
//                commonCode = "M_CHANNEL_ERROR";
//                commonMsg = "通道异常";
//                commonEnumMap.put(commonCode,commonMsg);
            }
            else if ((value.contains("关机") && value.contains("空号") && value.contains("停机")) || value.contains("号码问题") ){
                commonCode = "M_PHONE_DOWN_EMPTY_OFF_ERROR";
                commonMsg = "关机或空号或停机";
                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("关机") && value.contains("空号")){
                commonCode = "M_PHONE_DOWN_EMPTY_ERROR";
                commonMsg = "关机或空号";
                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("空号") && value.contains("停机")){
                commonCode = "M_PHONE_EMPTY_OFF_ERROR";
                commonMsg = "空号或停机";
                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("关机") && value.contains("停机")){
                commonCode = "M_PHONE_DOWN_OFF_ERROR";
                commonMsg = "关机或停机";
                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("关机")){
                commonCode = "M_PHONE_DOWN_ERROR";
                commonMsg = "关机";
                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("停机")){
                commonCode = "M_PHONE_OFF_ERROR";
                commonMsg = "停机";
                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("空号")){
                commonCode = "M_PHONE_NUMBER_EMPTY_ERROR";
                commonMsg = "空号";
                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("失败")){
                continue;
//                commonCode = "M_FAILED_ERROR";
//                commonMsg = "失败";
//                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("成功")){
                commonCode = "M_SUCCESS";
                commonMsg = "成功";
                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("业务")){
                continue;
//                commonCode = "M_BUSINESS_ERROR";
//                commonMsg = "业务异常";
//                commonEnumMap.put(commonCode,commonMsg);
            }
            else if ((value.contains("超过") && !value.contains("LICENSE"))|| value.contains("超频") || value.contains("超出") || value.contains("超1")
                     || value.contains("超限") || value.contains("频次") || value.contains("条") || value.contains("频繁")
                    || value.contains("月限制") || value.contains("次数限制")|| value.contains("空格限制")|| value.contains("号码限制")){
                commonCode = "M_OVER_LOAD_ERROR";
                commonMsg = "过载异常";
                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("账户") || value.contains("账号")){
                continue;
//                commonCode = "M_ACCOUNT_ERROR";
//                commonMsg = "账号异常";
//                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("服务")){
                commonCode = "M_OUT_OF_SERVICE_ERROR";
                commonMsg = "超出服务范围异常";
                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("资费")){
                continue;
//                commonCode = "M_EXPENSES_ERROR";
//                commonMsg = "资费异常";
//                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("重复")){
                commonCode = "M_REPEAT_ERROR";
                commonMsg = "数据重复异常";
                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("退订")){
                commonCode = "M_UNSUBSCRIBE_ERROR";
                commonMsg = "退订异常";
                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("终端")){
                commonCode = "M_TERMINAL_ERROR";
                commonMsg = "终端异常";
                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("网络")){
                continue;
//                commonCode = "M_NETWORK_ERROR";
//                commonMsg = "网络异常";
//                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("网关")){
                continue;
//                commonCode = "M_GATEWAY_ERROR";
//                commonMsg = "网关异常";
//                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("不存在")){
                commonCode = "M_NOT_EXIST_ERROR";
                commonMsg = "不存在异常";
                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("欠费") || value.contains("余额")){
                commonCode = "M_ARREARAGE_ERROR";
                commonMsg = "费用或余额不足异常";
                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("匹配")){
                continue;
//                commonCode = "M_MISMATCH_ERROR";
//                commonMsg = "匹配异常";
//                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("连接")){
                continue;
//                commonCode = "M_ESTABLISH_ERROR";
//                commonMsg = "建立连接异常";
//                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("端口")){
                continue;
//                commonCode = "M_PORT_ERROR";
//                commonMsg = "端口异常";
//                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("拦截")){
                continue;
//                commonCode = "M_INTERCEPTED_ERROR";
//                commonMsg = "拦截异常";
//                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("内容")){
                continue;
//                commonCode = "M_CONTENT_ERROR";
//                commonMsg = "内容不合规异常";
//                commonEnumMap.put(commonCode,commonMsg);
            }
            else if (value.contains("错误")){
                continue;
//                commonCode = "M_PARAM_ERROR";
//                commonMsg = "参数错误";
//                commonEnumMap.put(commonCode,commonMsg);
            }
            else{
                continue;
//                commonCode = "M_UNKNOWN_ERROR";
//                commonMsg = "其他错误";
//                commonEnumMap.put(commonCode,commonMsg);
            }
            String str = replacedKey + "(\""+ code + "\",\"" + value + "\",\"" +  commonCode + "\",\"" + commonMsg + "\"),";
            System.out.println(str); // 所有中文渠道错误码信息（筛选掉用户无关的错误）
        }
        Set<Map.Entry<String, String>> commonEnumMapEntries = commonEnumMap.entrySet();
        System.out.println("----------------------------------------统一异常信息---------------------------------------------");
        List<OutputToExcelModel> outputToExcelList = new ArrayList<>(); // 生成错误码excel
        for (Map.Entry<String, String> commonEnumMapEntry : commonEnumMapEntries) {
            String key = commonEnumMapEntry.getKey();
            String value = commonEnumMapEntry.getValue();
            String str = key + "(\"" + key + "\",\"" + value + "\"),";
//            System.out.println(str); // 打印枚举格式的结果
//            outputToExcelList.add(new OutputToExcelModel(key,value,"对应国内短信渠道及teleSign渠道错误码",null));
        }
        System.out.println("----------------------------------生成错误码excel开始----------------------------------");
//        outputToExcelList.add(new OutputToExcelModel("M_ACCEPTED","已被接收","对应twilio及teleSign渠道错误码",null));
//        outputToExcelList.add(new OutputToExcelModel("M_QUEUED","排队中","对应twilio及teleSign渠道错误码",null));
//        outputToExcelList.add(new OutputToExcelModel("M_SENDING","发送中","对应twilio及teleSign渠道错误码",null));
//        outputToExcelList.add(new OutputToExcelModel("M_SENT","已发送","对应twilio及teleSign渠道错误码",null));
//        outputToExcelList.add(new OutputToExcelModel("M_FAILED","失败","对应twilio及teleSign渠道错误码",null));
//        outputToExcelList.add(new OutputToExcelModel("M_DELIVERED","已投递","对应twilio及teleSign渠道错误码",null));
//        outputToExcelList.add(new OutputToExcelModel("M_UNDELIVERED","未投递","对应twilio及teleSign渠道错误码",null));
//        outputToExcelList.add(new OutputToExcelModel("M_RECEIVING","接收中","对应twilio及teleSign渠道错误码",null));
//        outputToExcelList.add(new OutputToExcelModel("M_RECEIVED","已接收","对应twilio及teleSign渠道错误码",null));
//        outputToExcelList.add(new OutputToExcelModel("M_READ","已读","对应twilio及teleSign渠道错误码",null));
//        String fileName = "C:\\Users\\hp\\Desktop\\工作文件\\系统错误码说明.xlsx";
//        write(fileName, OutputToExcelModel.class).sheet().head(OutputToExcelModel.class)
//                .registerWriteHandler(new CustomColumnWidthStrategy())
//                .registerWriteHandler(new CustomCellStyleStrategy()).doWrite(outputToExcelList);
        System.out.println("----------------------------------生成错误码excel结束----------------------------------");
//        readExcel1();
//        readExcel2();
//        readExcel3();
//        readExcel4();
    }


//    public static void main(String[] args) {
//        readExcel2();
//    }

    public static Map<String, String> readExcel1(){
        String fileName = "C:\\Users\\hp\\Desktop\\工作文件\\首网错误码解释.xlsx";
        ExcelListener<ShouWangModel> excelListener = new ExcelListener<>();
        read(fileName, ShouWangModel.class, excelListener).sheet().doRead();
        List<ShouWangModel> dataList = excelListener.getDataList();
        ArrayList<ShouWangModel> removedDuplicateList = dataList.stream().filter(x->x.getMsg() != null && !x.getMsg().equals("null"))
                .collect(Collectors.collectingAndThen(Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(ShouWangModel::getCode))), ArrayList::new));
//        System.out.println(JSON.toJSONString(dataList));
//        for (ShouWangModel sw : dataList) {
//            String str = "SW_" + sw.getCode() + "(\"" + sw.getChannel() + "\",\"" + sw.getCode() + "\",\"" +  sw.getMsg() + "\"),";
//            System.out.println(str);
//        }
        Map<String, String> map = new HashMap<>();
        for (ShouWangModel jm  : removedDuplicateList) {
            String code = jm.getCode().replace("-","F").replace("~","T")
                    .replace("：",":").replace("\t","");
            String prefix = "C";
            if (code.contains(":")){
                String[] split = code.split(":");
                for (String s : split) {
                    prefix = prefix + "_" + s;
                }
            } else if (code.contains(".")){
                String[] split = code.split("\\.");
                for (String s : split) {
                    prefix = prefix + "_" + s;
                }
            } else {
                prefix = prefix + "_" + code;
            }
            String str = prefix + "(\"" + jm.getChannel() + "\",\"" + jm.getCode() + "\",\"" +  jm.getMsg() + "\"),";
            map.put(jm.getCode(), jm.getMsg());
//            map.put(prefix, jm.getMsg());
//            System.out.println(str);
        }
        return map;
    }

    public static Map<String,String> readExcel2() {
        Map<String, String> map = new HashMap<>();
        String fileName = "C:\\Users\\hp\\Desktop\\工作文件\\梦网错误码解释.xlsx";
        ExcelListener<MengWangModel> excelListener = new ExcelListener<>();
        read(fileName, MengWangModel.class, excelListener).sheet().doRead();
        List<MengWangModel> dataList = excelListener.getDataList();
        ArrayList<MengWangModel> removedDuplicateList = dataList.stream().filter(x->x.getMsg() != null && !x.getMsg().equals("null"))
                .collect(Collectors.collectingAndThen(Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(MengWangModel::getCode))), ArrayList::new));

        for (MengWangModel jm  : removedDuplicateList) {
            String code = jm.getCode().replace("-","F").replace("~","T")
                    .replace("：",":").replace("\t","");
            String prefix = "C";
            if (code.contains(":")){
                String[] split = code.split(":");
                for (String s : split) {
                    prefix = prefix + "_" + s;
                }
            } else if (code.contains(".")){
                String[] split = code.split("\\.");
                for (String s : split) {
                    prefix = prefix + "_" + s;
                }
            } else {
                prefix = prefix + "_" + code;
            }
            String str = prefix + "(\"" + jm.getChannel() + "\",\"" + jm.getCode() + "\",\"" +  jm.getMsg() + "\"),";
            map.put(jm.getCode(), jm.getMsg());
//            map.put(prefix, jm.getMsg());
//            System.out.println(str);
//            System.out.println(prefix);
        }
        return map;
    }

    public static Map<String,String> readExcel3(){
        String fileName = "C:\\Users\\hp\\Desktop\\工作文件\\中鼎动力错误码解释.xlsx";
        ExcelListener<ZhongDingDongLiModel> excelListener = new ExcelListener<>();
        read(fileName, ZhongDingDongLiModel.class, excelListener).sheet().doRead();
        List<ZhongDingDongLiModel> dataList = excelListener.getDataList();
        ArrayList<ZhongDingDongLiModel> removedDuplicateList = dataList.stream().filter(x->x.getMsg() != null && !x.getMsg().equals("null"))
                .collect(Collectors.collectingAndThen(Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(ZhongDingDongLiModel::getCode))), ArrayList::new));
//        System.out.println(JSON.toJSONString(dataList));
        Map<String,String> map = new HashMap<>();
        for (ZhongDingDongLiModel zddl  : removedDuplicateList) {
            String code = zddl.getCode().replace("-","F").replace("~","T")
                    .replace("：",":").replace("～","T").replace("650(660\\99","650")
                    .replace("0054?","0054");
            String prefix = "C";
            if (code.contains(":")){
                String[] split = code.split(":");
                for (String s : split) {
                    prefix = prefix + "_" + s;
                }
            } else if (code.contains(".")){
                String[] split = code.split("\\.");
                for (String s : split) {
                    prefix = prefix + "_" + s;
                }
            } else {
                prefix = prefix + "_" + code;
            }
            String str = prefix + "(\"" + zddl.getChannel() + "\",\"" + zddl.getCode() + "\",\"" +  zddl.getMsg() + "\"),";
            map.put(zddl.getCode(), zddl.getMsg());
//            map.put(prefix, zddl.getMsg());
//            System.out.println(str);
        }
        return map;
    }

    public static Map<String,String> readExcel4(){
        Map<String,String> map = new HashMap<>();
        String fileName = "C:\\Users\\hp\\Desktop\\工作文件\\聚梦错误码解释.xlsx";
        ExcelListener<JuMengModel> excelListener = new ExcelListener<>();
        read(fileName, JuMengModel.class, excelListener).sheet().doRead();
        List<JuMengModel> dataList = excelListener.getDataList();
//        System.out.println(JSON.toJSONString(dataList));
        ArrayList<JuMengModel> removedDuplicateList = dataList.stream().filter(x->x.getMsg() != null && !x.getMsg().equals("null"))
                .collect(Collectors.collectingAndThen(Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(JuMengModel::getCode))), ArrayList::new));

        for (JuMengModel jm  : removedDuplicateList) {
            String code = jm.getCode().replace("-","F").replace("~","T").replace("：",":")
                    .replace("～","T").replace("650(660\\99","650").replace("0054?","0054");
            String prefix = "C";
            if (code.contains(":")){
                String[] split = code.split(":");
                for (String s : split) {
                    prefix = prefix + "_" + s;
                }
            } else if (code.contains(".")){
                String[] split = code.split("\\.");
                for (String s : split) {
                    prefix = prefix + "_" + s;
                }
            } else {
                prefix = prefix + "_" + code;
            }
            String str = prefix + "(\"" + jm.getChannel() + "\",\"" + jm.getCode() + "\",\"" +  jm.getMsg() + "\"),";
            map.put(jm.getCode(), jm.getMsg());
//            map.put(prefix, jm.getMsg());
//            System.out.println(str);
        }
        return map;
    }

    public static void readExcelTeleSign(){
        String fileName = "C:\\Users\\hp\\Desktop\\工作文件\\telesign错误码.xlsx";
        ExcelListener<TeleSignModel> excelListener = new ExcelListener<>();
        read(fileName, TeleSignModel.class, excelListener).sheet().doRead();
        List<TeleSignModel> dataList = excelListener.getDataList();
        for (TeleSignModel tl : dataList) {
            String msg = StringUtils.isEmpty(tl.getMsg()) ? tl.getDesc() : tl.getMsg();
            String code = tl.getCode().replace("-","F");
            String str = "M_" + code + "(\"" + tl.getCode() + "\",\"" +  msg + "\"),";
            System.out.println(str);
        }
    }

}
